﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddSubscriptionInfrastructure]
	@ServiceName servicenametype
AS
BEGIN
	SET NOCOUNT ON;

		DECLARE @sql nvarchar(max)

		DECLARE @DialogHandle uniqueidentifier
		DECLARE @OverflowServiceName sysname
		
		EXEC CreateSubscriptionService @ServiceName
		EXEC CreateOverflowService @OverflowServiceName OUTPUT
		
		DECLARE @Source servicenametype
		DECLARE @SourceId bigint
		
		SELECT @Source = [Source]
			  ,@SourceId = [ServiceMetadataId]
		FROM [ServiceMetadata]
		WHERE [Target] IS NULL
		
		SET @sql = N'BEGIN DIALOG @h FROM SERVICE [' + @Source + N'] TO SERVICE ''' + @ServiceName + ''''
		
		EXEC sp_executesql @sql, N'@h uniqueidentifier OUTPUT', @DialogHandle OUTPUT
		
		INSERT INTO [ServiceMetadata]
		(
			 [Source]
			,[Target]
			,[DialogHandle]
		)
		VALUES
		(
			 @Source
			,@ServiceName
			,@DialogHandle
		)
		
		SET @sql = N'BEGIN DIALOG @h FROM SERVICE [' + @Source + N'] TO SERVICE ''' + @OverflowServiceName + ''''
		SET @DialogHandle = NULL
		EXEC sp_executesql @sql, N'@h uniqueidentifier OUTPUT', @DialogHandle OUTPUT
		
		INSERT INTO [ServiceMetadata]
		(
			 [Source]
			,[Target]
			,[DialogHandle]
		)
		VALUES
		(
			 @Source
			,@OverflowServiceName
			,@DialogHandle
		)
		
		UPDATE [ServiceMetadata]
		SET [Source] = @OverflowServiceName
		WHERE [ServiceMetadataId] = @SourceId
		
		DECLARE @QueueName sysname
		
		SELECT @QueueName = q.name
		FROM sys.services s
		JOIN sys.service_queues q on q.object_id = s.service_queue_id
		WHERE s.name = @Source
		
		-- Mark Old overflow queue for garbage collection, except if the service is the RootService.
		IF @Source <> 'RootService'
			INSERT INTO [MarkedForGC] 
			(
				 [Service]
				,[Queue]
			)
			VALUES 
			(
				 @Source
				,@QueueName
			)

		-- Enable old overflow queue
		SET @sql = N'ALTER QUEUE ' + @QueueName + ' WITH ACTIVATION (STATUS = ON)';
		
		EXEC sp_executesql @sql
END
